package com.ivr.cm.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ivr.cm.dao.ISelfFilterDao;
import com.ivr.cm.util.BaseUtil;
import com.ivr.cm.vo.ConfigFilterVo;
import com.ivr.cm.vo.SelfFilterVo;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

/**
 * 过滤配置信息数据实现
 * @author liugeng
 * @date 2015-12-15
 * @version 1.0
 *
 */
public class SelfFilterDaoImpl extends JdbcDaoSupport implements ISelfFilterDao {
	private static Logger logger = Logger.getLogger(SelfFilterDaoImpl.class);
	/**获得所有的过滤配置信息列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<SelfFilterVo> getSelfFilterVoList() {
		String sql = "select filter.id, filter.num, filter.sp_number, ifnull(count_self.bill_count, 0) self_thres_index from " +
				"( SELECT bill.sp_number, COUNT(bill.mobile) bill_count FROM tb_ivr_user_bill as bill WHERE bill.add_date BETWEEN CURDATE() AND NOW() AND bill.fee_number > 1500 GROUP BY bill.sp_number ) as count_self " +
				"right join tb_ivr_config_filter as filter on filter.sp_number = count_self.sp_number " + 
				"where filter.status = 1 order by id";
		logger.info(String.format("获得所有的过滤配置信息列表[%s]", BaseUtil.logSQL(sql, null)));
		List<SelfFilterVo> selfFilterVoList = this.getJdbcTemplate().query(sql,rowMapper);
		if(selfFilterVoList != null && selfFilterVoList.size() > 0){
			return selfFilterVoList;
		}
		return null;
	}
	
	private ParameterizedRowMapper<SelfFilterVo> rowMapper = new ParameterizedRowMapper<SelfFilterVo>() {
		@Override
		public SelfFilterVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			SelfFilterVo selfFilterVo = new SelfFilterVo();
			selfFilterVo.setId(rs.getInt("id"));
			selfFilterVo.setNum(rs.getInt("num"));
			selfFilterVo.setSelf_thres_index(rs.getInt("self_thres_index"));
			selfFilterVo.setSp_number(rs.getString("sp_number"));
			return selfFilterVo;
		}
	};

}
